<?php


namespace App\Listeners;

use App\Models\ExcelFile;
use App\Models\User;
use Illuminate\Support\Facades\File;
use Illuminate\Support\Facades\Storage;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class UserListUtils
{
    public static function GetUserList($worker_id, $branch_list, $excel_record_id, $tag_list) {
//        try {
            $users = User::whereIn('branch_id', $branch_list)
                ->when($worker_id != null, function ($query) use ($worker_id) {
                    return $query->where('worker_id', $worker_id);
                })
                ->when(count($tag_list) > 0, function ($query) use ($tag_list) {
                    return $query->whereHas('tags', function ($q) use ($tag_list) {
                        $q->whereIn('tag_id', $tag_list);
                    });
                })
                ->with('worker')->with('bank_cards.bank')->with('pro')->with('city')->with('area')->with('orders')->with('tags')
                ->get();
            $spreadsheet = new Spreadsheet();
            $orderSheet = $spreadsheet->getActiveSheet();
            // 设置工作表名称
            $orderSheet->setTitle('打款记录表');
            // 设置表头
            $orderSheet->setCellValueByColumnAndRow(1, 1, '客户名称');      // A
            $orderSheet->setCellValueByColumnAndRow(2, 1, '客户经理');       // B
            $orderSheet->setCellValueByColumnAndRow(3, 1, '用户电话');       // C
            $orderSheet->setCellValueByColumnAndRow(4, 1, '性别');     // D
            $orderSheet->setCellValueByColumnAndRow(5, 1, '实名认证状态');     // E
            $orderSheet->setCellValueByColumnAndRow(6, 1, '银行名称');     // F
            $orderSheet->setCellValueByColumnAndRow(7, 1, '银行卡号');     // G
            $orderSheet->setCellValueByColumnAndRow(8, 1, 'email');     // H
            $orderSheet->setCellValueByColumnAndRow(9, 1, '详细地址');     // I
            $orderSheet->setCellValueByColumnAndRow(10, 1, '地址');     // J
            $orderSheet->setCellValueByColumnAndRow(11, 1, '支行名称');     // K
            $orderSheet->setCellValueByColumnAndRow(12, 1, '证件类型');      // L
            $orderSheet->setCellValueByColumnAndRow(13, 1, '证件号');      // M
            $orderSheet->setCellValueByColumnAndRow(14, 1, '生日');      // N
            $orderSheet->setCellValueByColumnAndRow(15, 1, '账号');      // O
            $orderSheet->setCellValueByColumnAndRow(16, 1, '订单数量');      // P
            $orderSheet->setCellValueByColumnAndRow(17, 1, '标签');      // Q
            $row = 2;
            foreach ($users as $user) {
                if ($row > 20000) {
                    break;
                }
                $user_name = $user->name;
                $worker_name = $user->worker->name ?? null;
                $user_phone = $user->contact_manner;
                $user_sex = $user->sex ?? null;
                if ($user_sex == 1) {
                    $sex = '男';
                } elseif ($user_sex == 2) {
                    $sex = '女';
                } else {
                    $sex = '未知';
                }
                if ($user->fdd_status==2) {
                    $fdd_status = '已认证';
                } else {
                    $fdd_status = '未认证';
                }
                $bank_cards = $user->bank_cards;
                $bank_name = null;
                $bank_num = null;
                $bank_branch = null;
                if (count($bank_cards) > 0) {
                    if (count($bank_cards) == 1) {
                        $bank_name = $bank_cards[0]['bank']['bank_name'];
                        $bank_num = $bank_cards[0]['bank_num'];
                        $bank_branch = $bank_cards[0]['bank_sum_name'];
                    } else {
                        foreach ($bank_cards as $bank_card) {
                            if ($bank_card['is_default'] == 1) {
                                $bank_name = $bank_card['bank']['bank_name'];
                                $bank_num = $bank_card['bank_num'];
                                $bank_branch = $bank_card['bank_sum_name'];
                            }
                        }
                    }
                }
                $email = $user->email;
                $address = $user->address;
                $add = null;
                if ($user->pro) {
                    $add = $user->pro->area_fullname;
                }
                if ($user->city) {
                    $add = $user->city->area_fullname;
                }
                if ($user->area) {
                    $add = $user->area->area_fullname;
                }
                $id_type = $user->id_type;
                $id_number = $user->id_number;
                try {
                    $birthday = date('Y-m-d', $user->birthday);
                } catch (\Throwable $th) {
                    $birthday = null;
                }
                $account = $user->account;
                $order_num = count($user->orders);
                $tags = $user->tags;
                $tagNames = '';
                if (count($tags) > 0) {
                    $tag_name = [];
                    foreach ($tags as $tag) {
                        $tag_name[] = $tag->name;
                    }
                    $tagNames = implode('/', $tag_name);
                }
                $orderSheet->setCellValueByColumnAndRow(1, $row, $user_name);      // A
                $orderSheet->setCellValueByColumnAndRow(2, $row, $worker_name);       // B
                $orderSheet->setCellValueByColumnAndRow(3, $row, $user_phone . "\t");       // C
                $orderSheet->setCellValueByColumnAndRow(4, $row, $sex);     // D
                $orderSheet->setCellValueByColumnAndRow(5, $row, $fdd_status);     // E
                $orderSheet->setCellValueByColumnAndRow(6, $row, $bank_name);     // F
                $orderSheet->setCellValueByColumnAndRow(7, $row, $bank_num . "\t");     // G
                $orderSheet->setCellValueByColumnAndRow(8, $row, $email);     // H
                $orderSheet->setCellValueByColumnAndRow(9, $row, $address);     // I
                $orderSheet->setCellValueByColumnAndRow(10, $row, $add);     // J
                $orderSheet->setCellValueByColumnAndRow(11, $row, $bank_branch);     // K
                $orderSheet->setCellValueByColumnAndRow(12, $row, $id_type);      // L
                $orderSheet->setCellValueByColumnAndRow(13, $row, $id_number . "\t");      // M
                $orderSheet->setCellValueByColumnAndRow(14, $row, $birthday);      // N
                $orderSheet->setCellValueByColumnAndRow(15, $row, $account);      // O
                $orderSheet->setCellValueByColumnAndRow(16, $row, $order_num);      // P
                $orderSheet->setCellValueByColumnAndRow(17, $row, $tagNames);      // Q
                $row = $row + 1;
            }
            $filename = 'U' . time() . '.xlsx';
            $writer = new Xlsx($spreadsheet);
            $path = public_path('excel/' . date("Ymd"));
            File::makeDirectory($path, 0777, true, true);
            $writer->save('../public/excel/' . date("Ymd") . '/' . $filename);
            unset($spreadsheet);
            $exists = Storage::disk('new_ftp')->put('htdocs/excel_files/' . date("Ymd") . '/' . $filename, file_get_contents('../public/excel/' . date("Ymd") . '/' . $filename));
            $file_url = env("FTP2_URL") . 'excel_files/' . date("Ymd") . '/' . $filename;
            $file_path = 'excel_files/' . date("Ymd") . '/' . $filename;
            if ($exists) {
                $excel_records = ExcelFile::find($excel_record_id);
                $excel_records->is_success = 1;
                $excel_records->file_url = $file_url;
                $excel_records->file_path = $file_path;
                $excel_records->save();
            } else {
                $excel_records = ExcelFile::find($excel_record_id);
                $excel_records->is_success = 2;
                $excel_records->save();
            }
//        } catch (\Throwable $th) {
//            $excel_records = ExcelFile::find($excel_record_id);
//            $excel_records->is_success = 2;
//            $excel_records->save();
//        }
    }
}
